package historymanager.tools;

import historymanager.servlets.ProgramInfo;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.log4j.Logger;



/**
 * Object-Model-Class for CSV-Update
 * 
 * @author Marcel Bardehle
 * @version 1.0
 *letze �nderung: log.error + �ndernd von char auf String f�r type und format
 */

public class CsvUpdate implements Runnable {

	static Logger log = Logger.getLogger(CsvUpdate.class);
	public Connection con;
	public Statement stmt1;
	ResultSet rs1;
	ProgramInfo programInfo = ProgramInfo.getInstance();
	
	@Override
	public void run() {

		// TODO Auto-generated method stub
		boolean serverConfigOK=false, measurepointConfigOK=false;
		
		
		programInfo.setdatabaseStatus("Initialisierung");
		log.debug("Server CSV Update Start");
		Configuration myConfiguration = Configuration.getInstance();
		try {
			if(serverUpdate()&&measurepointUpdate())
			{
			MeasurePointRequestorController measurePointRequestorController = MeasurePointRequestorController
					.getInstance();
			programInfo.setDatabaseSpace();
			programInfo.setdatabaseStatus("Update Datenpunkte");
			}
		
		} catch (Exception e) {
			programInfo.setdatabaseStatus("Datenbank nicht erreichbar");
			log.error("ServerConfig-File not found or database not available");
			log.error("Program stopped. Error in configfile or database");
		}

	}

	public boolean serverUpdate() throws Exception {
		// Variablendeklaration
		Configuration myConfiguration = Configuration.getInstance();
		String serverConfigPath = myConfiguration.getServerConfigPath();

		String serverAdress;
		String SBusNumber;
		String stationAlias;
		long ID;
		String serverAdressDB = null;
		String SBusNumberDB = null;
		String serverAliasDB = null;
		int serverActiveDB = 2;
		
		File serverConfigFile= new File(serverConfigPath);
		 if(serverConfigFile.exists()) {
		
		
		BufferedReader CSVFile = new BufferedReader(new FileReader(
				serverConfigPath));

		String dataRow = CSVFile.readLine(); // Erste Zeile der Konfig lesen und
												// verwerfen.
		dataRow = CSVFile.readLine(); // 2.Zeile lesen

		try {
			Context envContext;

			log.debug("Try to establish database connection ...");
			envContext = new InitialContext();
			DataSource ds = (DataSource) envContext.lookup(myConfiguration
					.getDataSourceContext());
			con = ds.getConnection(); // Datenbank-Verbindung erstellen
			stmt1 = con.createStatement();

			ServerActiveFalse(); // Alle Server auf inactive setzen

			while (dataRow != null) { // Eingelesene csv in Daten splitten

				String[] dataArray = dataRow.split(";");
				serverAdress = dataArray[0];
				SBusNumber = dataArray[1];
				stationAlias = dataArray[2];

				rs1 = stmt1 // Datensatz mit gleicher Serveradresse wie in CSV
							// abrufen
						.executeQuery("Select * from controllerunit WHERE serveraddress='"
								+ serverAdress + "'");
				if (rs1.next() == true) // Pr�fen ob Datensatz bereits
										// vorhanden
				{
					rs1 = stmt1 // Erneuter Abruf der Daten da durch rs.next der
								// 1.Datensatz schon �bersprungen wird
							.executeQuery("Select * from controllerunit WHERE serveraddress='"
									+ serverAdress + "'");

					while (rs1.next()) { // Datens�tze in Variablen schreiben
						ID = rs1.getLong("id");
						serverAdressDB = rs1.getString("serveraddress");
						SBusNumberDB = rs1.getString("stationname");
						serverAliasDB = rs1.getString("stationalias");
						serverActiveDB = rs1.getInt("active");
						log.debug("Alter Datensatz: " + ID + " "
								+ serverAdressDB + " " + SBusNumberDB + " "
								+ serverAliasDB + " " + serverActiveDB);

						UpdateServer(ID, serverAdress, SBusNumber, stationAlias); // Server-Daten
																					// updaten
						log.debug("Neuer Datensatz: " + ID + " " + serverAdress
								+ " " + SBusNumber + " " + stationAlias + " "
								+ serverActiveDB);
						log.debug("Update Dataset into controllerunit");

					}
				} else // neuen Datensatz anlegen
				{
					InsertServer(serverAdress, SBusNumber, stationAlias); // neuen
																			// Datensatz
																			// anlegen

					log.debug("Instert new dataset into controllerunit");
					log.debug("New dataset: " + serverAdress + " "
							+ SBusNumber + " " + stationAlias + " "
							+ "1");

				}
				dataRow = CSVFile.readLine(); // Read next line of data.
				rs1.close();
			}

		} catch (SQLException e) {
			 log.error("SQL-Database not available");
		}
		CSVFile.close(); // CSV-File schlie�en
		return true;
		 }
		 else{
			 log.error("ServerConfig-File not found"); 
				programInfo.setdatabaseStatus("Keine Serverconfig.csv gefunden. Datenabruf gestoppt");	
				return false;
		 }
	}// ServerUpdate End

	public boolean measurepointUpdate() throws Exception {
		Configuration myConfiguration = Configuration.getInstance();
		String measurepointConfigPath = myConfiguration
				.getMesurepointConfigPath();

		String serverAdress;
		String symbolname;
		String type;
		int registerAddress;
		String aliasName;
		String format;
		int decimaldigits;
		String unit;
		long ID=-1;
		long serverID=-1;

		String symbolnameDB = null;
		String typeDB;
		int registerAddressDB;
		String aliasNameDB = null;
		String formatDB;
		int decimaldigitsDB;
		String unitDB = null;
		long serverIDDB=-1;
		int measurepointActive=-1;

		
		File measurepointConfigFile= new File(measurepointConfigPath);
		 if(measurepointConfigFile.exists()) {
		
		BufferedReader CSVFile = new BufferedReader(new FileReader(
				measurepointConfigPath));

		String dataRow = CSVFile.readLine(); // Erste Zeile der Konfig lesen und
												// verwerfen.
		dataRow = CSVFile.readLine(); // 2.Zeile lesen

		try {
			MeasurepointActiveFalse(); // Alle Messpunkte auf inactive setzen
			while (dataRow != null) { // Eingelesene csv in Daten splitten

				String[] dataArray = dataRow.split(";");
				serverAdress = dataArray[0];
				symbolname = dataArray[1];
				type = dataArray[2]; 
				registerAddress = Integer.parseInt(dataArray[3]);
				aliasName = dataArray[4];
				format = dataArray[5];
				decimaldigits = Integer.parseInt(dataArray[6]);
				unit = dataArray[7];

				rs1 = stmt1 // Datensatz mit gleicher Serveradresse wie in CSV
							// abrufen
						.executeQuery("Select * from controllerunit WHERE serveraddress='"
								+ serverAdress + "'");
				if (rs1.next() == true)	// Erneuter Abruf der Daten da durch rs.next der
										// 1.Datensatz schon �bersprungen wird
				{
					rs1 = stmt1 // Datensatz mit gleicher Serveradresse wie in
								// CSV
							// abrufen
							.executeQuery("Select * from controllerunit WHERE serveraddress='"
									+ serverAdress + "'");
					while(rs1.next()){
					serverID = rs1.getLong("id"); // Server-ID speichern
					}
					
					
					String SQLStatement = "Select * from measurepoint WHERE controllerunit=? AND registeraddress=? ";
					PreparedStatement preparedStatement = con
							.prepareStatement(SQLStatement);
					preparedStatement.setLong(1, serverID);
					preparedStatement.setInt(2, registerAddress);
					rs1=preparedStatement.executeQuery();

					if (rs1.next() == true) // Pr�fen ob bereits ein Datensatz existiert
					{
						rs1=preparedStatement.executeQuery();	// Erneuter Abruf der Daten da durch rs.next der
																// 1.Datensatz schon �bersprungen wird

						while (rs1.next()) { // Datens�tze in Variablen
												// schreiben
							ID = rs1.getLong("id");
							serverIDDB = rs1.getLong("controllerunit");
							symbolnameDB = rs1.getString("symbolname");
							typeDB = rs1.getString("type");
							registerAddressDB= rs1.getInt("registeraddress");
							aliasNameDB = rs1.getString("aliasname");
							formatDB = rs1.getString("format");
							decimaldigitsDB= rs1.getInt("decimaldigits");
							unitDB = rs1.getString("unit");
							measurepointActive = rs1.getInt("active");
							
							log.debug("old dataset: " + ID + " "
									+ serverIDDB + " " + symbolnameDB + " "
									+ typeDB + " " + registerAddressDB + " " +  aliasNameDB + " " +  formatDB + " " +  decimaldigitsDB + " "
									+  unitDB + " " +  measurepointActive);

							
							UpdateMeasurepoint(ID,serverIDDB,symbolname,aliasName,type,registerAddress,
									format,decimaldigits,unit); // Server-Daten updaten
													
							log.debug("new dataset: "  + ID + " "
									+ serverIDDB + " " + symbolname + " "
									+ type + " " + registerAddress + " " +  aliasName + " " +  format + " " +  decimaldigits + " "
									+  unit + " " +  measurepointActive);
							log.debug("Update Dataset into measurepoint");

						}
					} else // neuen Datensatz anlegen
					{
						InsertMeasurepoint(serverID,symbolname,aliasName,type,registerAddress,
								format,decimaldigits,unit); // neuen
																				// Datensatz
																				// anlegen
						log.debug("Instert new dataset into measurepoint");
						log.debug("new dataset: "+ serverID + " " + symbolname + " "
								+ type + " " + registerAddress + " " +  aliasName + " " +  format + " " +  decimaldigits + " "
								+  unit);

					}
					dataRow = CSVFile.readLine(); // Read next line of data.
					rs1.close();
				} else {
					log.error("Server "+ serverAdress + "from measerpointconfig.csv not in serverconfig.csv");
				}

			}

		} catch (SQLException e) {
			log.error("SQL-Database not available");
		}
		CSVFile.close(); // CSV-File schlie�en
		 return true;
		 }
		 else{
			 log.error("MeasurepointConfig-File not found"); 
				programInfo.setdatabaseStatus("Keine Measurepointconfig.csv gefunden. Datenabruf gestoppt");	
			 return false;
		 }
		 
	}

	public void InsertServer(String serverAdress, String SBusNumber,
			String stationAlias) // Methode zum einf�gen eines neuen Servers
	{
		try {
			String SQLStatement = "INSERT INTO controllerunit(serveraddress,stationname,stationalias,active) VALUES( ?,?,?,?)";
			PreparedStatement preparedStatement = con
					.prepareStatement(SQLStatement);
			preparedStatement.setString(1, serverAdress);
			preparedStatement.setString(2, SBusNumber);
			preparedStatement.setString(3, stationAlias);
			preparedStatement.setInt(4, 1);
			preparedStatement.executeUpdate();
			preparedStatement.close();

		} catch (SQLException e) {
			log.error("SQL-error while insert new server into database");
		}

	} // Void insertServer end

	public void UpdateServer(long ID, String serverAdress, String SBusNumber,
			String stationAlias) // Methode zum Updaten eines neuen Servers
	{
		try {
			String SQLStatement = "UPDATE controllerunit SET serveraddress=?, stationname=?, stationalias=?, active=?  WHERE id=?";

			PreparedStatement preparedStatement = con
					.prepareStatement(SQLStatement);
			preparedStatement.setString(1, serverAdress);
			preparedStatement.setString(2, SBusNumber);
			preparedStatement.setString(3, stationAlias);
			preparedStatement.setInt(4, 1);
			preparedStatement.setLong(5, ID);

			preparedStatement.executeUpdate();
			preparedStatement.close();

		} catch (SQLException e) {
			log.error("SQL-error while update server in database");
		}

	} // Void UpdateServer end

	public void ServerActiveFalse() { // Schreibt beim 1.Initialisieren alle
										// gefundenen Server auf 0 (inactive)
		long ID;
		try {
			rs1 = stmt1.executeQuery("Select * from controllerunit");

			while (rs1.next()) { // Datens�tze in Variablen schreiben
				ID = rs1.getLong("id");

				String SQLStatement = "UPDATE controllerunit SET active=?  WHERE id=?";

				PreparedStatement preparedStatement = con
						.prepareStatement(SQLStatement);
				preparedStatement.setInt(1, 0);
				preparedStatement.setLong(2, ID);

				preparedStatement.executeUpdate();
				preparedStatement.close();

			}
		} catch (SQLException e) {
			log.error("SQL-error while set server to false into database");
		}

	}

	public void InsertMeasurepoint(long IDcontrollerunit, String symbolname,
			String aliasname, String Type, int registeraddress, String format,
			int decimaldigits, String unit) // Methode
											// zum
											// Updaten
											// eines
											// neuen
											// Messpunktes
	{
		try {
			String SQLStatement = "INSERT INTO measurepoint(controllerunit, symbolname, aliasname, "
					+ "Type, registeraddress, format, decimaldigits, unit, active) VALUES( ?,?,?,?,?,?,?,?,?)";

			PreparedStatement preparedStatement = con
					.prepareStatement(SQLStatement);
			preparedStatement.setLong(1, IDcontrollerunit);
			preparedStatement.setString(2, symbolname);
			preparedStatement.setString(3, aliasname);
			preparedStatement.setString(4, Type);
			preparedStatement.setInt(5, registeraddress);
			preparedStatement.setString(6, format); 
			preparedStatement.setInt(7, decimaldigits);
			preparedStatement.setString(8, unit);
			preparedStatement.setInt(9, 1);

			preparedStatement.executeUpdate();
			preparedStatement.close();

		} catch (SQLException e) {
			log.error("SQL-error while insert new measerepoint into database");

		}

	} // Void InsertMeasurepoint end

	public void UpdateMeasurepoint(long ID, long IDcontrollerunit,
			String symbolname, String aliasname, String Type,
			int registeraddress, String format, int decimaldigits, String unit) // Methode
																				// zum
																				// Updaten
																				// eines
																				// neuen
																				// Messpunktes
	{
		try {
			String SQLStatement = "UPDATE measurepoint SET controllerunit=?, symbolname=?, aliasname=?, Type=?,"
					+ " registeraddress=?, format=?, decimaldigits=?, unit=?, active=?  WHERE id=?";

			PreparedStatement preparedStatement = con
					.prepareStatement(SQLStatement);
			preparedStatement.setLong(1, IDcontrollerunit);
			preparedStatement.setString(2, symbolname);
			preparedStatement.setString(3, aliasname);
			preparedStatement.setString(4, Type); 
			preparedStatement.setInt(5, registeraddress);
			preparedStatement.setString(6, format); 
			preparedStatement.setInt(7, decimaldigits);
			preparedStatement.setString(8, unit);
			preparedStatement.setInt(9, 1);
			preparedStatement.setLong(10, ID);

			preparedStatement.executeUpdate();
			preparedStatement.close();

		} catch (SQLException e) {
			log.error("SQL-error while update measuerpoint in database");
		}

	} // Void UpdateMeasurepoint end

	public void MeasurepointActiveFalse() { // Schreibt beim 1.Initialisieren
											// alle
		// gefundenen Messpunkte auf 0 (inactive)
		long ID;
		try {
			rs1 = stmt1.executeQuery("Select * from measurepoint");

			while (rs1.next()) { // Datensätze in Variablen schreiben
				ID = rs1.getLong("id");

				String SQLStatement = "UPDATE measurepoint SET active=?  WHERE id=?";

				PreparedStatement preparedStatement = con
						.prepareStatement(SQLStatement);
				preparedStatement.setInt(1, 0);
				preparedStatement.setLong(2, ID);

				preparedStatement.executeUpdate();
				preparedStatement.close();

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.error("SQL-error while set measuerpoint to false in database");
		}
	}

} // Class-end
